package dao.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import model.Datentraeger;
import model.Film;
import model.ModelBase;
import dao.DAO;
import dao.FilmSqlDAO;
import dao.util.DAOException;
import dao.util.DaoAnnotation;
import dao.util.DaoContextEnum;
import dao.util.JDBCDateHelper;

@DaoAnnotation(className="FilmSqlDAO", use = DaoContextEnum.MYSQL, daoInterface = DAO.class)
public class FilmSqlDaoMySqlImpl implements FilmSqlDAO {
					private static final String SQL_INSERT = 	"INSERT INTO film(F_TimeStamp, F_Name, F_Hauptdarsteller, F_Genre, F_Laenge, F_D_id) " + 
																"VALUES (?, ?, ?, ?, ?, ?)";
					private static final String SQL_UPDATE = 	"UPDATE film " +
																"SET F_TimeStamp = ?, " + 
																"    F_Name = ?, " +
																"    F_Hauptdarsteller = ?, " + 
																"    F_Genre = ?," +
																"    F_Laenge = ?, " + 
																"    F_D_id = ? " + 
																"WHERE F_id = ? " + 
																"  AND F_TimeStamp = ? ";
	private static final String SQL_DELETE = 					"DELETE FROM film " +
																"WHERE F_id = ? " + 
																"  AND F_TimeStamp = ?";
	private static final String SQL_DELETE_FROM_DATENTRAEGER = 	"DELETE FROM film " +
																"WHERE F_Name = ? ";
	private static final String SQL_DELETE_ALL = 				"DELETE FROM film  ";
	private static final String SQL_SELECT_BY_ID = 				"SELECT F_id, F_TimeStamp, F_Name, F_Hauptdarsteller, F_Genre, F_Laenge, F_D_id " +
																"FROM film " + 
																"WHERE F_id = ?";
	private static final String SQL_SELECT_BY_NAMPT = 			"SELECT F_id, F_TimeStamp, F_Name, F_Hauptdarsteller, F_Genre, F_Laenge, F_D_id " +
																"FROM film " + 
																"WHERE F_Name like ?";
	private static final String SQL_SELECT_BY_DATENTR = 		"SELECT F_id, F_TimeStamp, F_Name, F_Hauptdarsteller, F_Genre, F_Laenge, F_D_id " +
																"FROM film " + 
																"WHERE F_D_id = ?";

	private static final String SQL_SELECT_ALL = 				"SELECT F_id, F_TimeStamp, F_Name, F_Hauptdarsteller, F_Genre, F_Laenge, F_D_id " +
																"FROM film ";

	private PreparedStatement insertStmt = null;
	private PreparedStatement updateStmt = null;
	private PreparedStatement deleteStmt = null;
	private PreparedStatement deleteFromDatentraegerStmt = null;
	private PreparedStatement deleteAllStmt = null;
	private PreparedStatement selectByIdStmt = null;
	private PreparedStatement selectByDtStmt = null;
	private PreparedStatement selectAllStmt = null;
	private PreparedStatement selectByNamPtStmt = null;

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#delete(java.sql.Connection, model.ModelBase)
	 */
	@Override
	public int delete(Connection connection, ModelBase film) 
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (film == null) 
		{
			throw new IllegalArgumentException("Produkt must not be null!");
		}
		if (film.getId() == null || film.getLastModifiedTimeStamp() == null) 
		{
			throw new IllegalArgumentException("Produkt has to have ID and TIMESTAMP");
		}
		int recordsCleared = 0;
		try {
			if (deleteStmt == null) {
				deleteStmt = connection.prepareStatement(SQL_DELETE);
			}
			deleteStmt.setLong(1, film.getId());
			deleteStmt.setTimestamp(2,
					JDBCDateHelper.toSqlTimestamp(film.getLastModifiedTimeStamp()));
			recordsCleared = deleteStmt.executeUpdate();

		} 
		catch (SQLException e) 
		{
			throw new DAOException("Film could not be cleared!", e);
		}

		return recordsCleared;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#deleteFromAbteilung(java.sql.Connection,
	 * model.ModelBase)
	 */
	@Override
	public int deleteFromDatentraeger(Connection con, Datentraeger datentraeger) 
	{
		if (con == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (datentraeger == null) 
		{
			throw new IllegalArgumentException("Abteilung must not be null!");
		}
		if (datentraeger.getId() == null) 
		{
			throw new IllegalArgumentException("Abteilung has to have ID and TIMESTAMP");
		}
		int recordsCleared = 0;
		try {
			if (deleteFromDatentraegerStmt == null) 
			{
				deleteFromDatentraegerStmt = con.prepareStatement(SQL_DELETE_FROM_DATENTRAEGER);
			}

			deleteFromDatentraegerStmt.setLong(1, datentraeger.getId());
			recordsCleared = deleteFromDatentraegerStmt.executeUpdate();

		} catch (SQLException e) {
			throw new DAOException("Film from Datentraeger could not be cleared!", e);
		}

		return recordsCleared;
	}

	@Override
	public void deleteAll(Connection connection) 
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		try 
		{
			if (deleteAllStmt == null) 
			{
				deleteAllStmt = connection.prepareStatement(SQL_DELETE_ALL);
			}

			deleteAllStmt.executeUpdate();

		} 
		catch (SQLException e) 
		{
			throw new DAOException("Filme could not be cleared!", e);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#findAll(java.sql.Connection)
	 */
	@Override
	public List<Film> findAll(Connection connection)
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		List<Film> result = new ArrayList<Film>();
		try 
		{
			if (selectAllStmt == null) 
			{
				selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
			}
			ResultSet rs = selectAllStmt.executeQuery();

			while (rs.next()) 
			{
				Film film = new Film(rs.getLong(F_ID), rs.getString(F_NAME),
						rs.getString(F_HAUPTDARSTELLER),
						rs.getString(F_GENRE), rs.getInt(F_LAENGE));
				film.setLastModifiedTimeStamp(JDBCDateHelper.toDate(rs
						.getTimestamp(F_TIMESTAMP)));

				film.setDatentraeger(new DatentraegerSqlDaoMySqlImpl().findById(connection, rs.getLong(F_D_ID)));
				film.initDatInfo();

				result.add(film);
			}
			rs.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException("Error at finding all from Film!", e);
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#findAllFromDatentraeger(java.sql.Connection,
	 * model.ModelBase)
	 */
	@Override
	public List<Film> findAllFromDatentraeger(Connection connection,Long datentraeger) 
	{
		if (connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (datentraeger == null) 
		{
			throw new IllegalArgumentException("Datentraeger must not be null!");
		}
		
		List<Film> result = new ArrayList<Film>();
		Film film;
		try {
			if (selectByDtStmt == null) 
			{
				selectByDtStmt = connection.prepareStatement(SQL_SELECT_BY_DATENTR);
			}
			selectByDtStmt.setLong(1, datentraeger);
			ResultSet rs = selectByDtStmt.executeQuery();

			while (rs.next()) 
			{
				film = new Film(rs.getLong(F_ID), rs.getString(F_NAME),
						rs.getString(F_HAUPTDARSTELLER),
						rs.getString(F_GENRE), rs.getInt(F_LAENGE));
				film.setLastModifiedTimeStamp(JDBCDateHelper.toDate(rs
						.getTimestamp(F_TIMESTAMP)));

				film.setDatentraeger(new DatentraegerSqlDaoMySqlImpl().findById(connection, datentraeger));
				film.initDatInfo();

				result.add(film);
			}
			rs.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException("Error at finding all Filme from Datentraeger!", e);
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#findById(java.sql.Connection, java.lang.Long)
	 */
	@Override
	public Film findById(Connection connection, Long id)
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (id == null) 
		{
			throw new IllegalArgumentException("ID must not be null!");
		}
		Film foundFilm = null;
		try 
		{
			if (selectByIdStmt == null) 
			{
				selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
			}

			selectByIdStmt.setLong(1, id);
			ResultSet rs = selectByIdStmt.executeQuery();

			while (rs.next()) 
			{
				foundFilm = new Film(	id, 
										rs.getString(F_NAME),
										rs.getString(F_HAUPTDARSTELLER),
										rs.getString(F_GENRE), 
										rs.getInt(F_LAENGE));
				foundFilm.setLastModifiedTimeStamp(JDBCDateHelper.toDate(
										rs.getTimestamp(F_TIMESTAMP)));

				foundFilm.setDatentraeger(new DatentraegerSqlDaoMySqlImpl().findById(connection, rs.getLong(F_D_ID)));
				foundFilm.initDatInfo();
			}
			rs.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException("Film could not be found!", e);
		}

		return foundFilm;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#findByNamePart(java.sql.Connection, java.lang.String)
	 */
	@Override
	public List<Film> findByNamePart(Connection connection, String part) 
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (part == null) 
		{
			throw new IllegalArgumentException("Namepart must not be null!");
		}
		if (part.trim().length() <= 2) 
		{
			throw new IllegalArgumentException("Namepart has to have more than two chars!");
		}
		List<Film> result = new ArrayList<Film>();
		Film film;
		try 
		{
			if (selectByNamPtStmt == null) 
			{
				selectByNamPtStmt = connection.prepareStatement(SQL_SELECT_BY_NAMPT);
			}
			selectByNamPtStmt.setString(1, part);
			ResultSet rs = selectByNamPtStmt.executeQuery();

			while (rs.next()) 
			{
				film = new Film(rs.getLong(F_ID), 
								rs.getString(F_NAME),
								rs.getString(F_HAUPTDARSTELLER),
								rs.getString(F_GENRE), 
								rs.getInt(F_LAENGE));
				film.setLastModifiedTimeStamp(JDBCDateHelper.toDate(rs.getTimestamp(F_TIMESTAMP)));

				film.setDatentraeger(new DatentraegerSqlDaoMySqlImpl().findById(connection, rs.getLong(F_D_ID)));
				film.initDatInfo();

				result.add(film);
			}
			rs.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException("Error at finding all films with namepart: " + part + "!", e);
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.FilmSQLDAO#save(java.sql.Connection, model.Film)
	 */
	@Override
	public int save(Connection connection, ModelBase data) 
	{
		if (connection == null) 
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (data == null) 
		{
			throw new IllegalArgumentException("Film must not be null!");
		}
		Film film = (Film) data;

		if (film.getDatentraeger() == null) 
		{
			throw new IllegalArgumentException("Film has to have an Datentraeger!");
		}
		int recordsAffected = 0;
		recordsAffected = (film.isNew() ? insert(connection, film) : update(connection, film));
		return recordsAffected;
	}

	private int update(Connection connection, Film film) 
	{
		int recordsAffected = 0;
		Date now = JDBCDateHelper.killUtilNanos(new Date());

		try 
		{
			if (updateStmt == null) 
			{
				updateStmt = connection.prepareStatement(SQL_UPDATE, PreparedStatement.RETURN_GENERATED_KEYS);
			}
			updateStmt.setTimestamp(1, JDBCDateHelper.toSqlTimestamp(now));
			updateStmt.setString(2, film.getName());
			updateStmt.setString(3, film.getHauptdarsteller());
			updateStmt.setString(4, film.getGenre());
			updateStmt.setInt(5, film.getLaenge());
			updateStmt.setLong(6, film.getDatentraeger().getId());

			updateStmt.setLong(7, film.getId());
			updateStmt.setTimestamp(8, JDBCDateHelper.toSqlTimestamp(film.getLastModifiedTimeStamp()));

			recordsAffected = updateStmt.executeUpdate();

			film.setLastModifiedTimeStamp(now);

		} 
		catch (SQLException e) 
		{
			throw new DAOException("Film could not be updated!", e);
		}

		return recordsAffected;
	}

	private int insert(Connection connection, Film film) 
	{
		int recordsAffected = 0;
		Date now = JDBCDateHelper.killUtilNanos(new Date());

		try 
		{
			if (insertStmt == null) 
			{
				insertStmt = connection.prepareStatement(SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
			}
			insertStmt.setTimestamp(	1, JDBCDateHelper.toSqlTimestamp(now));
			insertStmt.setString(		2, film.getName());
			insertStmt.setString(		3, film.getHauptdarsteller());
			insertStmt.setString(		4, film.getGenre());
			insertStmt.setInt(			5, film.getLaenge());
			insertStmt.setLong(			6, film.getDatentraeger().getId());
			recordsAffected = 			insertStmt.executeUpdate();

			ResultSet generatedKeys = 	insertStmt.getGeneratedKeys();

			if (generatedKeys.next()) 
			{
				long currentID = generatedKeys.getLong(1);
				film.setId(currentID);
				film.setLastModifiedTimeStamp(now);
			}
			generatedKeys.close();
		} 
		catch (SQLException e) 
		{
			throw new DAOException("Film could not be inserted!", e);
		}
		return recordsAffected;
	}

	@Override
	public int delete(Connection con, Film data) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Film findByName(Connection con, String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Film save(Connection con, Film data) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Film read(InputStream in) throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void write(OutputStream out, Film data) throws IOException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Collection<Film> readMultiple(InputStream in) throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void writeMultiple(OutputStream out, Collection<Film> data)
			throws IOException {
		// TODO Auto-generated method stub
		
	}


}
